from dbutils.persistent_db import PersistentDB
import sqlite3


class SqlitePool(object):
    __pool = None

    def __init__(self, database='mySQLite.sqlite3', max_usage=10, closeable=False):
        self.cur = None
        self.conn = None
        self.database = database
        self.max_usage = max_usage,
        self.closeable = closeable,
        self.config = {'database': self.database}
        """创建连接池对象  单例设计模式(每个线程中只创建一个连接池对象)  PersistentDB为每个线程提供专用的连接池"""
        if self.__pool is None:
            self.__class__.__pool = PersistentDB(sqlite3, maxusage=10, closeable=False, **self.config)

    def get_connection(self):
        self.conn = self.__pool.connection()
        self.cur = self.conn.cursor()

    def release_connection(self):
        self.cur.close()
        self.conn.close()

    def run_query(self, query, *var):
        self.get_connection()
        error = None
        try:
            self.cur.execute(query, var)
            if self.cur.description is not None:
                columns = [row[0] for row in self.cur.description]
                data = self.cur.fetchall()
                json_data = [dict(zip(columns, row)) for row in data]
            else:
                json_data = []
            self.conn.commit()
        except Exception as e:
            error = e
            json_data = None

        finally:
            self.release_connection()
        return json_data, error


"""
SQLite 由于线程安全机制 不支持 PooledDB 线程共享连接模式   故使用PersistentDB 线程专用连接模式 为每个线程单独开连接池
PooledDB()中的参数解释自行查找
"""
if __name__ == '__main__':
    # SqlitePool不是单例模式，只是每次都返回了相同的__pool
    sqlite_pool = SqlitePool('/Users/fengwensen/projects/code_analysis/pqctech/code_analysis.db', 10, False)
    # time.sleep(2)
    sql = f"SELECT * FROM code_analysis_results;"
    # 查询
    sqlite_pool.run_query(sql)
    # 插入
    sql =f"""INSERT INTO code_analysis_results (encryption, decryption, signature, verification, algorithm)
        VALUES (25,1, 2, 3, 4, 5);
        """
    sqlite_pool.run_query(sql)

    sql = f"""delete from code_analysis_results where id=24"""
    sqlite_pool.run_query(sql)
